package com.Alan.dao.user;

import com.Alan.dao.BaseDao;
import com.Alan.pojo.User;
import com.mysql.cj.util.StringUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @CreateDate 2021/8/9 13:15
 */
public class UserDaoImpl implements UserDao{
    @Override
    public User getLoginUser(Connection connection, String userCode) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = null;

        if (connection != null) {
            String sql = "SELECT * FROM user WHERE userCode = ?;";
            Object[] params = {userCode};
            try {
                resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql, params);
                if (resultSet.next()) {
                    user = new User();
                    user.setId(resultSet.getInt("id"));
                    user.setUserCode(resultSet.getString("userCode"));
                    user.setUserName(resultSet.getString("userName"));
                    user.setUserPassword(resultSet.getString("userPassword"));
                    user.setGender(resultSet.getString("gender"));
                    user.setBirthday(resultSet.getDate("birthday"));
                    user.setPhone(resultSet.getString("phone"));
                    user.setAddress(resultSet.getString("address"));
                    user.setUserRole(resultSet.getInt("userRole"));
                    user.setCreatedByUserCode(resultSet.getString("createdByUserCode"));
                    user.setCreatedByUserName(resultSet.getString("createdByUserName"));
                    user.setCreationDate(resultSet.getDate("creationDate"));
                    user.setModifyByUserCode(resultSet.getString("modifyByUserCode"));
                    user.setModifyByUserName(resultSet.getString("modifyByUserName"));
                    user.setModifyDate(resultSet.getDate("modifyDate"));
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("ERROR:UserDaoImpl -> getLoginUser");
            }

//        释放资源
            BaseDao.closeResource(null, preparedStatement, resultSet);

        }
        return user;
    }

    @Override
    public int updatePassword(Connection connection, int id, String password) {
        PreparedStatement preparedStatement = null;
        int updateRows = 0;

        if (connection != null){
            String sql = "UPDATE `user` SET userPassword = ? WHERE id = ?;";
            Object[] params = {password, id};
            try {
                updateRows = BaseDao.execute(connection, preparedStatement, sql, params);
                BaseDao.closeResource(null, preparedStatement, null);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("UserDaoImpl--updatePassword错误");
            }

            BaseDao.closeResource(null, preparedStatement, null);
        }
        return updateRows;
    }

    @Override
    public int getUserCount(Connection connection, String userName, int userRole) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int userCount = 0;

        if (connection != null){
            StringBuffer sql = new StringBuffer();
            sql.append("select count(1) userCount from user u, role r where u.userRole = r.id");
            List<Object> list = new ArrayList<Object>();

            if (!StringUtils.isNullOrEmpty(userName)){
                sql.append(" and u.userName like ?");
                list.add("%" + userName + "%");
            }
            if (userRole > 0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);
            }

            Object[] params = list.toArray();
            System.out.println("提示根据条件查询User总数SQL=" + sql.toString());

            try {
                resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql.toString(), params);
                if (resultSet.next()){
                    userCount = resultSet.getInt("userCount");
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("UserDaoImpl -> getUserCount");
            }

            BaseDao.closeResource(null, preparedStatement, resultSet);
        }

        return userCount;
    }

    @Override
    public List<User> getUserList(Connection connection, String userName, int userRole, int startRow, int pageRows) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<User> listUser = new ArrayList<User>();;

        if (connection != null){
            StringBuffer sql = new StringBuffer();
            sql.append("select u.*, r.roleName userRoleName from user u, role r where u.userRole = r.id");
            List<Object> list = new ArrayList<Object>();

            if (!StringUtils.isNullOrEmpty(userName)){
                sql.append(" and u.userName like ?");
                list.add("%" + userName + "%");
            }
            if (userRole > 0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);
            }

            sql.append(" order by u.id limit ?, ?");
            list.add(startRow);
            list.add(pageRows);
            Object[] params = list.toArray();
            System.out.println("查询用户列表SQl=" + sql.toString());

            try {
                resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql.toString(), params);
                while (resultSet.next()){
                    User user = new User();
                    user.setId(resultSet.getInt("id"));
                    user.setUserCode(resultSet.getString("userCode"));
                    user.setUserName(resultSet.getString("userName"));
                    user.setUserPassword(resultSet.getString("userPassword"));
                    user.setGender(resultSet.getString("gender"));
                    user.setBirthday(resultSet.getDate("birthday"));
                    user.setPhone(resultSet.getString("phone"));
                    user.setAddress(resultSet.getString("address"));
                    user.setUserRole(resultSet.getInt("userRole"));
                    user.setCreatedByUserCode(resultSet.getString("createdByUserCode"));
                    user.setCreatedByUserName(resultSet.getString("createdByUserName"));
                    user.setCreationDate(resultSet.getDate("creationDate"));
                    user.setModifyByUserCode(resultSet.getString("modifyByUserCode"));
                    user.setModifyByUserName(resultSet.getString("modifyByUserName"));
                    user.setModifyDate(resultSet.getDate("modifyDate"));
//                    用户角色
                    user.setUserRoleName(resultSet.getString("userRoleName"));
                    listUser.add(user);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("UserDaoImpl -> getUserList");
            }

            BaseDao.closeResource(null, preparedStatement, resultSet);
        }
        return listUser;
    }

    @Override
    public int addUser(Connection connection, User user) {
        PreparedStatement preparedStatement = null;
        int insertRows = 0;
        if (connection != null){
            String sql = "insert into user (userCode, userName, userPassword, gender, birthday, " +
                    "phone, address, userRole, createdByUserCode,createdByUserName, creationDate)" +
                    "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
            Object[] params = {
                    user.getUserCode(), user.getUserName(), user.getUserPassword(), user.getGender(), user.getBirthday(),
                    user.getPhone(), user.getAddress(), user.getUserRole(), user.getCreatedByUserCode(), user.getCreatedByUserName(), user.getCreationDate()
//                    ,user.getModifyByUserCode(), user.getModifyByUserName(), user.getModifyDate()
            };
            System.out.println(Arrays.toString(params));
            try {
                insertRows = BaseDao.execute(connection, preparedStatement, sql, params);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("UserDaoImpl -> addUser");
            }

            BaseDao.closeResource(null, preparedStatement, null);
        }
        return insertRows;
    }

    @Override
    public User getUserInformation(Connection connection, int userId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = null;

        if (connection != null){
            String sql = "select u.*, r.roleName roleName from user u, role r where u.userRole = r.id and u.id = ?;";
            user = new User();
            Object[] params = {userId};
            try {
                resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql, params);
                if (resultSet.next()) {
                    user = new User();
                    user.setId(resultSet.getInt("id"));
                    user.setUserCode(resultSet.getString("userCode"));
                    user.setUserName(resultSet.getString("userName"));
                    user.setUserPassword(resultSet.getString("userPassword"));
                    user.setGender(resultSet.getString("gender"));
                    user.setBirthday(resultSet.getDate("birthday"));
                    user.setPhone(resultSet.getString("phone"));
                    user.setAddress(resultSet.getString("address"));
                    user.setUserRole(resultSet.getInt("userRole"));
                    user.setCreatedByUserCode(resultSet.getString("createdByUserCode"));
                    user.setCreatedByUserName(resultSet.getString("createdByUserName"));
                    user.setCreationDate(resultSet.getDate("creationDate"));
                    user.setModifyByUserCode(resultSet.getString("modifyByUserCode"));
                    user.setModifyByUserName(resultSet.getString("modifyByUserName"));
                    user.setModifyDate(resultSet.getDate("modifyDate"));

                    user.setUserRoleName(resultSet.getString("roleName"));
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("ERROR:UserDaoImpl -> getUserInformation");
            }

            BaseDao.closeResource(null, preparedStatement, resultSet);
        }
        return user;
    }

    @Override
    public int updateUser(Connection connection, User user) {
        PreparedStatement preparedStatement = null;
        int updateRows = 0;
        if (connection != null){
            String sql = "update user set " +
                    "userName = ?, gender = ?, birthday = ?, phone = ?, address = ?, userRole = ?, " +
                    "modifyByUserCode = ?, modifyByUserName = ?, modifyDate = ? where id = ?;";
            Object[] params = {
                    user.getUserName(), user.getGender(), user.getBirthday(), user.getPhone(), user.getAddress(), user.getUserRole(),
                    user.getModifyByUserCode(), user.getModifyByUserName(), user.getModifyDate(), user.getId()
            };

            try {
                updateRows = BaseDao.execute(connection, preparedStatement, sql, params);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("ERROR:UserDaoImpl -> updateUser");
            }

            BaseDao.closeResource(null, preparedStatement, null);
        }

        return updateRows;
    }

    @Override
    public int deleteUser(Connection connection, int userId) {
        PreparedStatement preparedStatement = null;
        int updateRows = 0;

        if (connection != null){
            String sql = "delete from user where id = ?;";
            Object[] params = {userId};
            try {
                updateRows = BaseDao.execute(connection, preparedStatement, sql, params);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("ERROR:UserDaoImpl -> deleteUser");
            }

            BaseDao.closeResource(null, preparedStatement, null);
        }
        return updateRows;
    }

    @Test
    public void text() throws ParseException {
        UserDaoImpl userDao = new UserDaoImpl();
        User admin = userDao.getLoginUser(BaseDao.getConnection(), "admin");
        System.out.println(admin.toString());
        /*int userCount = userDao.getUserCount(BaseDao.getConnection(), "李明", 2);
        System.out.println(userCount);*/

        /*List<User> userList = userDao.getUserList(BaseDao.getConnection(), "", 0, 0, 2);
        for (User user : userList) {
            System.out.println(user.toString());
            System.out.println(user.getAge());
        }*/

        /*String strDate = "1999-12-01 00:00:00";
        User user = new User();
        user.setUserCode("test");
        user.setUserName("test");
        user.setUserPassword("test");
        user.setGender("男");
        user.setBirthday(UtilsAlan.strToSqlDate(strDate));
        user.setPhone("12345678911");
        user.setAddress("住在服务器");
        user.setUserRole(3);
        user.setCreatedByUserCode("liming");
        user.setCreatedByUserName("李明");
        user.setCreationDate(UtilsAlan.strToSqlDate("1999-12-1 00:01:01"));

        int insertRow = userDao.addUser(BaseDao.getConnection(), user);
        System.out.println("insertRow:" + insertRow);*/

        /*User userInformation = userDao.getUserInformation(BaseDao.getConnection(), 1);
        System.out.println(userInformation.toString());*/



        /*String strDate = "1999-12-01 00:00:00";
        User user = new User();
        user.setId(5);
        user.setUserCode("test");
        user.setUserName("testetsttes");
        user.setUserPassword("test");
        user.setGender("女");
        user.setBirthday(UtilsAlan.strToSqlDate(strDate));
        user.setPhone("12345678911");
        user.setAddress("住在服务器");
        user.setUserRole(3);
        user.setCreatedByUserCode("liming");
        user.setCreatedByUserName("李明");
        user.setCreationDate(UtilsAlan.strToSqlDate("1999-12-1 00:01:01"));

        int insertRow = userDao.updateUser(BaseDao.getConnection(), user);
        System.out.println("insertRow:" + insertRow);*/

        /*int i = userDao.deleteUser(BaseDao.getConnection(), 11);
        System.out.println(i);*/
    }
}
